﻿using System.Data.SqlClient;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using QL_Truong_Hoc;

namespace Extend.SqlString
{
    public class libSqlString
    {
        //public  SqlConnection sqlCn;
        private static string strErrorMessage;
        private static int intErrorNumber;
        private static string mErrorMsg;
        private static int mErrorCode;
       
        /// <summary>
        /// String: Câu thông báo lỗi khi truy cập SQL
        /// </summary>
        /// 
        public static string ErrorMessage
        {
            get
            {
                return strErrorMessage;
            }
        }
        /// <summary>
        /// int: Số lỗi khi truy cập SQL
        /// </summary>
        public static int ErrorNumber
        {
            get
            {
                return intErrorNumber;
            }
        }

        /// <summary>
        /// Thực thi 1 StoreProcedure có trả về dữ liệu là 1 bảng
        /// </summary>
        /// <return>
        /// DataTable
        /// </return>>
        public static DataTable ThucThiStoreTraVeBang(
            string TenStoreProcedure,
            SqlParameter[] sqlParam)
            
        {
            SqlConnection sqlCn = new SqlConnection();
            DataTable dtbTmp = new DataTable();
            try
            {

                //sqlCn = MoKetNoi();
                //sqlCn = MoKetNoi();
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandTimeout = 2000;
                sqlCmd.Connection = sqlCn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = TenStoreProcedure;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlParam[i]);
                }
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = sqlCmd;
                sqlDA.Fill(dtbTmp);
                DongKetNoi(sqlCn);
            }
            catch (SqlException sqlEx)
            {
                strErrorMessage = sqlEx.Message;
                intErrorNumber = sqlEx.Number;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();

            }
            return dtbTmp;
        }
        /// <summary>
        /// Thuc thi 1 store tra ve du lieu la dataset
        /// </summary>
        /// <param name="TenStoreProcedure"></param>
        /// <param name="sqlParam"></param>
        /// <returns></returns>
        public static DataSet ThucThiStoreTraVeDataSet(
            string TenStoreProcedure,
            SqlParameter[] sqlParam)
        {
            SqlConnection sqlCn = new SqlConnection();
            DataSet dtbTmp = new DataSet();
            try
            {
                sqlCn = MoKetNoi();
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandTimeout = 2000;
                sqlCmd.Connection = sqlCn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = TenStoreProcedure;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlParam[i]);
                }
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = sqlCmd;
                sqlDA.Fill(dtbTmp);
                DongKetNoi(sqlCn);
            }
            catch (SqlException sqlEx)
            {
                strErrorMessage = sqlEx.Message;
                intErrorNumber = sqlEx.Number;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();

            }
            return dtbTmp;
        }
        /// <summary>
        /// Thực thi 1 StoreProcedure không trả lại giá trị
        /// </summary>
        public static void ThucThiStore(string TenStoreProcedure, SqlParameter[] sqlParam)
        {
            SqlConnection sqlCn = new SqlConnection();
            try
            {
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandTimeout = 2000;
                sqlCn = MoKetNoi();
                sqlCmd.Connection = sqlCn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = TenStoreProcedure;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlParam[i]);
                }
                sqlCmd.ExecuteNonQuery();
                DongKetNoi(sqlCn);
            }
            catch (SqlException sqlEx)
            {
                strErrorMessage = sqlEx.Message;
                intErrorNumber = sqlEx.Number;
            }

            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
        }
        /// <summary>
        /// Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là 1 bảng
        /// </summary>
        /// <return>
        /// DataTable
        /// </return>>
        public static DataTable ThucThiCauTruyVan_TraVeBang(string strSQL)
        {
            DataTable ds = new DataTable();
            if (string.IsNullOrEmpty(strSQL))
                return ds;
            SqlConnection sqlCn = new SqlConnection();
            sqlCn = MoKetNoi();
            SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
            Adapter.SelectCommand.CommandTimeout = 180;
            if (sqlCn == null)
                return ds;
            try
            {
                Adapter.Fill(ds);
                DongKetNoi(sqlCn);

            }
            catch (SqlException E)
            {
                string strDescriptionError = E.Message;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                    sqlCn.Dispose();
            }
            return ds;
        }

        public static DataTable ThucThiCauTruyVanTimCT(string so_CT)
        {
            DataTable ds = new DataTable();
            if (string.IsNullOrEmpty(so_CT))
                return ds;
            SqlConnection sqlCn = new SqlConnection();
            sqlCn = MoKetNoi();
            SqlDataAdapter Adapter = new SqlDataAdapter("Select * from item_cpMain_qty Where (so_CT='" + so_CT + "')", sqlCn);
            Adapter.SelectCommand.CommandTimeout = 180;
            if (sqlCn == null)
                return ds;
            try
            {
                Adapter.Fill(ds);
                DongKetNoi(sqlCn);
            }
            catch (SqlException E)
            {
                string strDescriptionError = E.Message;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
            return ds;
        } 
        /// <summary>
        /// Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là DataSet
        /// </summary>
        /// <return>
        /// DataSet
        /// </return>>
        public static DataSet ThucThiCauTruyVan_TraVeDataSet(string strSQL)
        {
            SqlConnection sqlCn = new SqlConnection();
            sqlCn = MoKetNoi();
            SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
            DataSet ds = new DataSet();
            try
            {
                Adapter.Fill(ds);
                DongKetNoi(sqlCn);
            }
            catch (SqlException E)
            {
                string strDescriptionError = E.Message;
            }
            return ds;
        }
        /// <summary>
        /// Thực thi 1 câu lệnh SQL dạng select không trả lại giá trị
        /// </summary>
        public static string ThucThiLenhSQL(string strSQL)
        {
            if (string.IsNullOrEmpty(strSQL))
                return string.Empty;
            SqlConnection sqlCn = new SqlConnection();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandTimeout = 2000;
            sqlCn = MoKetNoi();
            sqlCommand.Connection = sqlCn;
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandText = strSQL;
            sqlCommand.CommandType = CommandType.Text;
            try
            {
                intErrorNumber = sqlCommand.ExecuteNonQuery();
                mErrorCode = 0;
                mErrorMsg = "";
                DongKetNoi(sqlCn);
            }
            catch (SqlException ex)
            {
                intErrorNumber = ex.Number;
                mErrorMsg = ex.Message;
                System.Console.Write(ex.StackTrace);
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
            return mErrorCode.ToString() + ":" + mErrorMsg;

        }

        public static bool Test_ketnoi()
        {
            SqlConnection sqlCntest = new SqlConnection();
            sqlCntest = MoKetNoi();
            if (sqlCntest == null)
                return false;
            else
            {
                if (sqlCntest.State == ConnectionState.Open)
                    sqlCntest.Close();
                return true;
             }
 
        }

        public libSqlString()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #region KetNoi CSDL
        public static SqlConnection MoKetNoi()
        {

            SqlConnection cnn = new SqlConnection(Command.Connect.ConnectString);
            try
            {
                cnn.Open();
            }
            catch
            {
                return null;
            }
            return cnn;
        }

        public static void DongKetNoi(SqlConnection sqlCn)
        {
            if (sqlCn != null)
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
        }

        #endregion
    }
}
